import java.io.*;
import java.net.*;
import java.util.*;

//Para trabajar con la BBDD.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.net.ssl.*; //Para trabajar con la sesión.
import java.security.cert.Certificate; //Para trabajar con certificados.
import java.security.cert.X509Certificate;

public class ConnectionProcessor3 implements Runnable
{
  private EM_Server3 server;

  private Socket socket;

  private DataInputStream din;

  private DataOutputStream dout;

  public ConnectionProcessor3( EM_Server3 server, Socket socket ) {
    this.server = server;
    this.socket = socket;

    new Thread( this ).start();
  }
  
  public void run() {
    try {
      InputStream in = socket.getInputStream();
      OutputStream out = socket.getOutputStream();

      din = new DataInputStream( in );
      dout = new DataOutputStream( out );

      //Control de identidad: ¡sin pasar id ninguno!
      SSLSession sesio = ((SSLSocket)socket).getSession();
      Certificate cert[] = sesio.getPeerCertificates();
      String dadesCert = ((X509Certificate)cert[0]).getIssuerDN().getName();
      int n = dadesCert.indexOf("OU=")-2; //El nombre de empresa no debe contener el caracter =.
      String empresa = dadesCert.substring(3,n);
      System.out.println("with certificate: "+dadesCert);
      
      //Conexión con la BBDD.
      Class.forName("com.mysql.jdbc.Driver");
      Connection connect = DriverManager.getConnection("jdbc:mysql://localhost/em?"+"user=user&password=password");
      
      //informamos de cuántos experimentos han finalizado.
      Statement statement_count = connect.createStatement();
      ResultSet n_finalitzats = statement_count.executeQuery("SELECT COUNT(id) FROM em.EXPERIMENTS WHERE STATUS = 'Finalitzat' AND EMPRESA = '"+empresa+"';");
      n_finalitzats.next();
      int count = n_finalitzats.getInt("COUNT(id)");
      dout.writeInt(count);

      //Los enviamos uno a uno
      Statement statement_finalitzats = connect.createStatement();
      ResultSet finalitzats = statement_finalitzats.executeQuery("SELECT * FROM em.EXPERIMENTS WHERE STATUS = 'Finalitzat' AND EMPRESA = '"+empresa+"';");
      while(finalitzats.next()){

	dout.writeUTF(finalitzats.getString("IDEXPERIMENT"));
	String path = finalitzats.getString("RESULTATS");
	int id = finalitzats.getInt("id");
	//Transferimos los ficheros con los resultados del experimento:
	File dir = new File(path);
	String files[] = dir.list(); //nombres de archivo
	dout.writeInt(files.length); //# de archivos a enviar.
	  for (int i=0; i<files.length;i++){
	    dout.writeUTF(files[i]);
	    BufferedReader bin = new BufferedReader(new FileReader(id+"Res/"+files[i]));
	    String dades;
	    while ((dades = bin.readLine()) != null){
	      dout.writeUTF(dades);
	}
	dout.writeUTF( "Fin!" );
	
      }

    }
      
      //Necesitamos el ok para cambiar el estado.
      String aux = din.readUTF(); //Dame lo mio!!
      String ok = din.readUTF();
      if (ok.equals("Todo bien!")){
	GestorBD gestor = new GestorBD();
	gestor.modificar("em","user","password","UPDATE em.EXPERIMENTS SET STATUS = 'Baixat' WHERE EMPRESA = '"+empresa+"' AND STATUS = 'Finalitzat'");
      }
   
      } catch( Exception e ) {
      try {
        socket.close();
      } catch( IOException ie2 ) {
        System.out.println( "Error closing socket "+socket );
      }

      server.removeConnection( this );

      System.out.println( "Closed connection from socket "+socket );
    }
  }

}
